package com.awspaas.user.apps.cpq.controller;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;

import com.actionsoft.bpms.bo.engine.BO;
import com.actionsoft.bpms.commons.database.RowMap;
import com.actionsoft.bpms.commons.mvc.view.ResponseObject;
import com.actionsoft.bpms.server.UserContext;
import com.actionsoft.bpms.util.DBSql;
import com.actionsoft.sdk.local.SDK;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

public class CPQ_UpdateMouleMsgsWeb {

	public String updateMouleMsgs(UserContext userContext,String pid, String bindId, String datas,String rowData,String types) {
		ResponseObject ro = ResponseObject.newOkResponse();
		Workbook workbookwl = new XSSFWorkbook();

		if(types.equals("否")) {
			try {
				BO bo=SDK.getBOAPI().query("BO_EU_CPQ_MODULE_RECORD").bindId(pid).detail();
				System.out.println("BINDID:"+pid);
				System.out.println("bo:"+bo);
			    JSONArray list=JSONObject.parseArray(datas);
				List<BO> mblists=SDK.getBOAPI().query("BO_EU_CPQ_PROJECT_MSG_PARAMETER_GRIDS").bindId(bindId).list();
				//if(list.size()<1) {
					//return ro.toString();
					//}

				Map<String, BO> mbMap=new HashMap<>();
				Map<String, JSONObject> pMap = new HashMap<String, JSONObject>();
				Map<String, String> nameMap = new HashMap<String, String>();

				if(mblists.size()>0) {
					for (int i = 0; i < mblists.size(); i++) {
						BO bo2 = mblists.get(i);
						mbMap.put(bo2.getString("PARMENTIER_NO"), bo2);
						String bh =bo2.getString("PARMENTIER_NO");
						String value =  bo2.getString("PARMENTIER_VALUE");
						String name =  bo2.getString("PARMENTIER_NAME");
						String dw =  bo2.getString("DW");
						JSONObject jsonObject=new JSONObject();
						//判断value是否为数据
						if( NumberUtils.isCreatable(value)) {
							jsonObject.put("type", "1");//值为1者表示value为数值
						}else {
							jsonObject.put("type", "0");//值为0者表示value为字符串
						}
						jsonObject.put("value", value);
						pMap.put(bh, jsonObject);
						nameMap.put(bh, name);

					}
				}

				//存放修改后的参数信息
				Map<String, String> changeMap = new HashMap<String, String>();
				Map<String, String> changeMap1 = new HashMap<String, String>();
				//存放新增的参数信息
				Map<String, JSONObject> addMap = new HashMap<String, JSONObject>();
			if(list.size()>0) {
				for (int i = 0; i < list.size(); i++) {
					JSONObject json = (JSONObject) list.get(i);
					//BO json = list.get(i);
					String bh = (String) json.get("PARMENTIER_NO");
					String value = (String) json.get("PARMENTIER_VALUE");
					String name = (String) json.get("PARMENTIER_NAME");
					String dw = (String) json.get("DW");
					JSONObject jsonObject=new JSONObject();
					//判断value是否为数据
					if( NumberUtils.isCreatable(value)) {
						jsonObject.put("type", "1");//值为1者表示value为数值
					}else {
						jsonObject.put("type", "0");//值为0者表示value为字符串
					}
					jsonObject.put("value", value);
					pMap.put(bh, jsonObject);
					nameMap.put(bh, name);
					if(mbMap.containsKey(bh)) {
						BO bo2=mbMap.get(bh);
						String oldvalue=bo2.getString("PARMENTIER_VALUE");
						if(!oldvalue.equals(value)) {
							changeMap.put(bo2.getId(), value);
							changeMap1.put(bh, value);
						}
					}else {
						addMap.put(bh, json);
					}
				}
			}

				// 设置参数值到单元格中
					JSONArray cslist = new JSONArray();
					for (String key : pMap.keySet()) {
						cslist.add(pMap.get(key));
					}
					Sheet sheet = workbookwl.createSheet("Sheet1");
					Map<String, String> noMap = new HashMap<>();
					for (int i = 0; i < cslist.size(); i++) {
						int num = i + 1;
						Cell cellB1 = sheet.createRow(i).createCell(1);
						JSONObject jsonObject = (JSONObject) cslist.get(i);
						// BO jsonObject = list.get(i);
						String bh = (String) jsonObject.get("PARMENTIER_NO");
						String type = jsonObject.getString("type");
						if (type.equals("0")) {
							cellB1.setCellValue(jsonObject.getString("value"));
							System.out.println("cellB1:" + cellB1.getStringCellValue());
						} else {
							String value = jsonObject.getString("value");
							double numValue = Double.parseDouble(value);
							cellB1.setCellValue(numValue);
							System.out.println("cellB1:" + cellB1.getNumericCellValue());
						}
						noMap.put(bh, "B$" + num);
					}
					//获取对应的模块信息,存在修改的参数信息
					List<RowMap> maps=new ArrayList<>();
					if(changeMap1.size()>0) {
						String keys="";
						for (String key : changeMap1.keySet()) {
							if(keys.isEmpty()) {
								keys="'"+key+"'";
							}else {
								keys+=",'"+key+"'";
							}
						}
						String  modelSql="select   k.*,h.ID,h.WLMC,h.WLBM,h.WLJSGS,h.CLF,h.RGF,h.WXWG,h.CGF,h.DJ,"
								+ "h.ZJ,h.ZCGF,h.ZGSF,h.ZWX,h.ZCLF,h.BINDID from  "
								+ "BO_EU_CPQ_MODULE_RECORD_GRID h,("
								+ "select a.ID as BID,a.XDXH,a.MKBH,a.SL,b.BINDID,b.SLJSGZ,b.MKSL,b.ZZFBL "
								+ "from  BO_EU_CPQ_PROJECT_MSG_GRIDS a, " +
								"(select  c.MKBM,n.PARMENTIER_NO,c.BINDID,c.SLJSGZ,c.MKSL,c.ZZFBL from  "
								+ "BO_EU_CPQ_MODULE_RECORD c,BO_EU_CPQ_MODULE_RECORD_PARAMETERS n "
								+ "where c.BINDID=n.BINDID and c.GLPID=? ) b  "
								+ "where a.MKBH=b.MKBM and a.MKBH!=? and  a.BINDID=?  and b.PARMENTIER_NO in("+keys+") ) k "
								+ "where h.BINDID=k.BINDID";
						 maps=DBSql.getMaps(modelSql, new Object[] {bindId,bo.getString("MKBM"),bindId});
						 List<String> sqlList=new ArrayList<>();
						 List<String> sqlLists=new ArrayList<>();
						 List<String> bjlList=new ArrayList<>();
						 List<String> jllLists=new ArrayList<>();
						    if(maps.size()>0) {
						    	Map<String, List<RowMap>> idMap = new HashMap<>();
						    	for (RowMap rowMap : maps) {
						    		String bm=rowMap.getString("MKBM");
						    		if(idMap.containsKey(bm)) {
						    		List<RowMap>	bms=idMap.get(bm);
						    		bms.add(rowMap);
						    		idMap.put(bm, bms);
						    		}else {
						    			List<RowMap> bMaps=new ArrayList<RowMap>();
						    			bMaps.add(rowMap);
						    			idMap.put(rowMap.getString("MKBM"), bMaps);
						    		}

								}
						    	//对新增的模块进行计算
						    	String sql="update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ=?,CLF=?,RGF=?,WXWG=?,CGF=?,ZFWZYF=?,ZJ=?,ZCLF=?,ZGSF=?,ZWXWG=?,ZCGF=?,ZZFWZYF,SL=? where ID=?";
						    	for (String key1 : idMap.keySet()) {
						    		List<RowMap> materMap = new ArrayList<>();
									List<RowMap> rowMaps=idMap.get(key1);
									double MKDJ = 0;// 期货价
									double CLF = 0;// 期货价材料费
									double RGF = 0;// 人工费
									double WXWG = 0;// 外协费
									double CGF = 0;// 采购费
									for (int i = 0; i < rowMaps.size(); i++) {
										  RowMap rowMap=rowMaps.get(i);
										  String equation = rowMap.getString("WLJSGS");
										  // 添加空值检查
										  if (equation == null || equation.trim().isEmpty()) {
											  System.out.println("警告：WLJSGS字段为空，跳过处理");
											  continue;
										  }
										  Document document = Jsoup.parse(equation);
										  String newQuation=document.text();
										  // 添加newQuation的空值检查
										  if (newQuation == null) {
											  System.out.println("警告：解析后的公式为空，跳过处理");
											  continue;
										  }
										  if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
											  System.out.println("参数计算公式："+newQuation);
												// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
												for (String key : pMap.keySet()) {
													// 添加key的空值检查
													if (key != null) {
														int num = newQuation.indexOf(key);
														if (num > -1) {
															// 添加noMap.get(key)的空值检查
															String replacement = noMap.get(key);
															if (replacement != null) {
																newQuation = newQuation.replace(key, replacement);
															} else {
																System.out.println("警告：参数 " + key + " 在noMap中未找到对应的替换值，跳过替换");
															}
														}
													}
												}
												System.out.println("转换后的参数计算公式："+newQuation);
												// 计算出物料数量和当前模块的物料成本价格
												FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
												Cell cellB1 = sheet.createRow(cslist.size()+i).createCell(2);
												cellB1.setCellFormula(newQuation);
												formulaEvaluator.evaluateFormulaCell(cellB1);
												// 获取公式计算结果
												CellValue cellValue = formulaEvaluator.evaluate(cellB1);
												System.out.println("The result of the formula is: " + cellValue.getNumberValue());
												String price1 = rowMap.getString("DJ").isEmpty()?"0":rowMap.getString("DJ");
												//String zl = bo.getString("ZL").isEmpty()?"0":bo.getString("ZL");
												//String tj = bo.getString("TJ").isEmpty()?"0":bo.getString("TJ");
												String clf = rowMap.getString("CLF").isEmpty()?"0":rowMap.getString("CLF");
												String rgf = rowMap.getString("RGF").isEmpty()?"0":rowMap.getString("RGF");
												String wx = rowMap.getString("WXWG").isEmpty()?"0":rowMap.getString("WXWG");
												String cgf = rowMap.getString("CGF").isEmpty()?"0":rowMap.getString("CGF");
												double total1 = Double.parseDouble(price1) * cellValue.getNumberValue();
												//double zltotal = Double.parseDouble(zl) * cellValue.getNumberValue();
												//double tjtotal = Double.parseDouble(tj) * cellValue.getNumberValue();
												double clftotal1 = Double.parseDouble(clf) * cellValue.getNumberValue();
												double rgftotal = Double.parseDouble(rgf) * cellValue.getNumberValue();
												double wxtotal = Double.parseDouble(wx) * cellValue.getNumberValue();
												double cgftotal = Double.parseDouble(cgf) * cellValue.getNumberValue();
												System.out.println("物料名称:"+rowMap.getString("WLMC")+"单价："+price1+",总价："+total1);
												// 将数量和成本价格放入物料map集合中

												rowMap.put("SL", cellValue.getNumberValue());
												rowMap.put("ZJ", total1);
												//bo.set("ZZL", zltotal);
												//bo.set("ZTJ", tjtotal);
												rowMap.put("ZCGF", cgftotal);
												rowMap.put("ZGSF", rgftotal);
												rowMap.put("ZWX", wxtotal);
												rowMap.put("ZCLF",clftotal1);
												 String upSql= "update BO_EU_CPQ_MODULE_RECORD_GRID set SL= '"+cellValue.getNumberValue()+"',ZJ= '"+total1+"' ,ZCGF='"+cgftotal+"',ZGSF='"+rgftotal+"',ZWX='"+wxtotal+"',ZCLF='"+clftotal1+"' where WLBM='"+rowMap.getString("ID")+"' ; ";
													//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
													sqlList.add(upSql);

										  }
										  materMap.add(rowMap);
									}
									// 计算出模块的单价，每个物料总价相加
									double mkPrice1 = 0;
									double zcgfPrice = 0;
									double zgsfPrice = 0;
									double zwxPrice = 0;
									double zclfPrice1 = 0;
									for (RowMap rowMap : materMap) {
										String zjString1 = rowMap.getString("ZJ");
										String zcgfString = rowMap.getString("ZCGF");
										String zgsfString = rowMap.getString("ZGSF");
										String zwxString = rowMap.getString("ZWX");
										String zclfString1 = rowMap.getString("ZCLF");
										mkPrice1 = mkPrice1 + Double.parseDouble(zjString1);
										zcgfPrice = zcgfPrice + Double.parseDouble(zcgfString);
										zgsfPrice = zgsfPrice + Double.parseDouble(zgsfString);
										zwxPrice = zwxPrice + Double.parseDouble(zwxString);
										zclfPrice1 = zclfPrice1 + Double.parseDouble(zclfString1);
									}

								   //计算直发物资运费
										RowMap data=rowMaps.get(0);
										String processId=data.getString("BINDID");
										List<BO> zfList=SDK.getBOAPI().query("BO_EU_BO_EU_CPQ_MODULE_MSG_STRAIGHT_HAIR").bindId(processId).list();
										double ZFYF = 0;// 期货价
										if(zfList.size()>0) {
				                	  for (int i = 0; i < zfList.size(); i++) {
				                		  BO bo2=zfList.get(i);
										  String sljsgs=bo2.getString("JSGS");//直发运费数量计算公式；
				                		  String yfdj=bo2.getString("YFBZ").isEmpty()?"0":bo2.getString("YFBZ");//运费单价；
				                		  String SL=bo2.getString("SL").isEmpty()?"0":bo2.getString("SL");//运费单价；
				                		  double price =Double.parseDouble(yfdj);
				                		  //double sl =Double.parseDouble(SL);
				 	        			 // BigDecimal sl = new BigDecimal(SL);
				                		 // double zj =price*sl;
									    //ZFYF=ZFYF+zj;
										// 添加空值检查
										if (sljsgs == null || sljsgs.trim().isEmpty()) {
											System.out.println("警告：JSGS字段为空，跳过处理");
											continue;
										}
										Document document = Jsoup.parse(sljsgs);
										String newQuation=document.text();
										// 添加newQuation的空值检查
										if (newQuation == null) {
											System.out.println("警告：解析后的公式为空，跳过处理");
											continue;
										}
										 if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
											  for (String key : pMap.keySet()) {
													// 添加key的空值检查
													if (key != null) {
														int num = newQuation.indexOf(key);
														if (num > -1) {
															// 添加noMap.get(key)的空值检查
															String replacement = noMap.get(key);
															if (replacement != null) {
																newQuation = newQuation.replace(key, replacement);
															} else {
																System.out.println("警告：参数 " + key + " 在noMap中未找到对应的替换值，跳过替换");
															}
														}
													}
												}
											  FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
												Cell cellB1 = sheet.createRow(cslist.size()+rowMaps.size()+1+i).createCell(2);
												cellB1.setCellFormula(newQuation);
												formulaEvaluator.evaluateFormulaCell(cellB1);
												// 获取公式计算结果
												CellValue cellValue = formulaEvaluator.evaluate(cellB1);
												System.out.println("The result of the formula is: " + cellValue.getNumberValue());
												ZFYF=ZFYF+cellValue.getNumberValue()*price;
												 String upSql= "update BO_EU_CPQ_MODULE_RECORD_STRAIGHT_HAIR set SL= '"+cellValue.getNumberValue()+"',YFJG= '"+cellValue.getNumberValue()*price+"'  where ID='"+bo2.getId()+"' ; ";
													//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
													sqlLists.add(upSql);

									}else {
										String sl=bo2.getString("SL").isEmpty()?"0":bo2.getString("SL");//运费数量;
										 double num=Double.parseDouble(sl);
										ZFYF=ZFYF+num*price;
									}

				                	  }
				                  }
										MKDJ = mkPrice1+ZFYF;
										CLF = zclfPrice1;
										RGF = zgsfPrice;
										WXWG = zwxPrice;
										CGF = zcgfPrice;
										//计算模块数量

									String jsgs=data.getString("SLJSGZ");//模块数量计算公式；
									// 添加空值检查
									if (jsgs == null || jsgs.trim().isEmpty()) {
										System.out.println("警告：SLJSGZ字段为空，跳过处理");
										jsgs = "0"; // 设置为默认值
									}
									Document document = Jsoup.parse(jsgs);
									String newQuation=document.text();
									// 添加newQuation的空值检查
									if (newQuation == null) {
										System.out.println("警告：解析后的公式为空，跳过处理");
										newQuation = "0"; // 设置为默认值
									}
									  if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
										  for (String key : pMap.keySet()) {
												// 添加key的空值检查
												if (key != null) {
													int num = newQuation.indexOf(key);
													if (num > -1) {
														// 添加noMap.get(key)的空值检查
														String replacement = noMap.get(key);
														if (replacement != null) {
															newQuation = newQuation.replace(key, replacement);
														} else {
															System.out.println("警告：参数 " + key + " 在noMap中未找到对应的替换值，跳过替换");
														}
													}
												}
											}
										  FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
											Cell cellB1 = sheet.createRow(cslist.size()+rowMaps.size()+zfList.size()+3).createCell(2);
											cellB1.setCellFormula(newQuation);
											formulaEvaluator.evaluateFormulaCell(cellB1);
											// 获取公式计算结果
											CellValue cellValue = formulaEvaluator.evaluate(cellB1);
											System.out.println("The result of the formula is: " + cellValue.getNumberValue());
											double total1 = MKDJ * cellValue.getNumberValue();
											double clftotal1 = CLF * cellValue.getNumberValue();
											double rgftotal = RGF * cellValue.getNumberValue();
											double wxtotal =WXWG * cellValue.getNumberValue();
											double cgftotal = CGF * cellValue.getNumberValue();
											double yftotal = ZFYF * cellValue.getNumberValue();
											//更新模块记录清单信息
											BigDecimal clf = new BigDecimal(CLF);
											BigDecimal rgf = new BigDecimal(RGF);
											BigDecimal djnum = new BigDecimal(MKDJ);
											 String bl=data.getString("ZZFBL").isEmpty()?"0":data.getString("ZZFBL");
												BigDecimal blnum = new BigDecimal(bl);
												BigDecimal num = new BigDecimal("100");
												BigDecimal num1 = new BigDecimal("1");
												BigDecimal zzf=	clf.add(rgf).divide(num1.subtract(blnum.divide(num)), 4).multiply(blnum.divide(num));
											 String upSql= "update BO_EU_CPQ_MODULE_RECORD set MKDJ= '"+MKDJ+"',CLF= '"+CLF+"' ,RGF='"+RGF+"',WXWG='"+WXWG+"',CGF='"+CGF+"',ZFWZYF='"+ZFYF+"',MKSL='"+cellValue.getNumberValue()+"',"
											 		+ "ZZF='"+zzf+"',DJZZF='"+zzf.add(djnum)+"' where MKBM='"+key1+"' and GLPID='"+bindId+"' ; ";
												//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
												bjlList.add(upSql);
											//更新报价记录模块信息
										//String sql="update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ=?,CLF=?,RGF=?,WXWG=?,CGF=?,ZFWZYF=?,ZJ=?,ZCLF=?,ZGSF=?,ZWXWG=?,ZCGF=?,ZZFWZYF,SL=? where ID=?";
											String upSql1= "update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ= '"+MKDJ+"',CLF= '"+CLF+"' ,RGF='"+RGF+"',WXWG='"+WXWG+"',CGF='"+CGF+"',ZFWZYF='"+ZFYF+"',"
													        + "ZJ='"+total1+"',ZCLF='"+clftotal1+"',ZGSF='"+rgftotal+"',ZWXWG='"+wxtotal+"',ZCGF='"+cgftotal+"',ZZFWZYF='"+yftotal+"',SL='"+cellValue.getNumberValue()+"'"
													 		+ " where  ID='"+data.getString("BID")+"' ; ";
														//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
											jllLists.add(upSql1);
										 // DBSql.update(sql, new Object[] {MKDJ,CLF,RGF,WXWG,CGF,ZFYF,total1,clftotal1,rgftotal,wxtotal,cgftotal,yftotal,cellValue.getNumberValue()});
									  }else {
										  String sl=data.getString("SL").isEmpty()?"0":data.getString("SL");//模块数量;
										  double num=Double.parseDouble(sl);
										  double total1 = MKDJ * num;
										  double clftotal1 = CLF * num;
										  double rgftotal = RGF * num;
										  double wxtotal =WXWG * num;
									      double cgftotal = CGF * num;
									      double yftotal = ZFYF * num;
									    //更新模块记录清单信息
											BigDecimal clf = new BigDecimal(CLF);
											BigDecimal rgf = new BigDecimal(RGF);
											BigDecimal djnum = new BigDecimal(MKDJ);
											 String bl=data.getString("ZZFBL").isEmpty()?"0":data.getString("ZZFBL");
												BigDecimal blnum = new BigDecimal(bl);
												BigDecimal num2 = new BigDecimal("100");
												BigDecimal num1 = new BigDecimal("1");
												BigDecimal zzf=	clf.add(rgf).divide(num1.subtract(blnum.divide(num2)), 4).multiply(blnum.divide(num2));
											 String upSql= "update BO_EU_CPQ_MODULE_RECORD set MKDJ= '"+MKDJ+"',CLF= '"+CLF+"' ,RGF='"+RGF+"',WXWG='"+WXWG+"',CGF='"+CGF+"',ZFWZYF='"+ZFYF+"',MKSL='"+num+"',"
											 		+ "ZZF='"+zzf+"',DJZZF='"+zzf.add(djnum)+"' where MKBM='"+key1+"' and GLPID='"+bindId+"' ; ";
												//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
												bjlList.add(upSql);
											//更新报价记录模块信息
										//String sql="update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ=?,CLF=?,RGF=?,WXWG=?,CGF=?,ZFWZYF=?,ZJ=?,ZCLF=?,ZGSF=?,ZWXWG=?,ZCGF=?,ZZFWZYF,SL=? where ID=?";
											String upSql1= "update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ= '"+MKDJ+"',CLF= '"+CLF+"' ,RGF='"+RGF+"',WXWG='"+WXWG+"',CGF='"+CGF+"',ZFWZYF='"+ZFYF+"',"
													        + "ZJ='"+total1+"',ZCLF='"+clftotal1+"',ZGSF='"+rgftotal+"',ZWXWG='"+wxtotal+"',ZCGF='"+cgftotal+"',ZZFWZYF='"+yftotal+"',SL='"+num+"'"
													 		+ " where  ID='"+data.getString("BID")+"' ; ";
														//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
											jllLists.add(upSql1);
										 // DBSql.update(sql, new Object[] {MKDJ,CLF,RGF,WXWG,CGF,ZFYF,total1,clftotal1,rgftotal,wxtotal,cgftotal,yftotal,sl});

									  }

								}
						    }
						    if(jllLists.size()>0) {
								 String[] array3 = sqlList.toArray(new String[jllLists.size()]);
								 DBSql.batch(array3);
							}
							if(bjlList.size()>0) {
								String[] array2 = sqlLists.toArray(new String[bjlList.size()]);
								DBSql.batch(array2);
							}
						    if(sqlList.size()>0) {
								 String[] array3 = sqlList.toArray(new String[sqlList.size()]);
								 DBSql.batch(array3);
							}
							if(sqlLists.size()>0) {
								String[] array2 = sqlLists.toArray(new String[sqlLists.size()]);
								DBSql.batch(array2);
							}
						 }
					//判断当前模块是否在报价记录模块子表中是否已经创建，如果一存在则进行更新，不存在则进行创建
					JSONObject row=JSONObject.parseObject(rowData);
					String id=row.getString("ID");
					BO data=SDK.getBOAPI().query("BO_EU_CPQ_PROJECT_MSG_GRIDS").addQuery("ID=", id).detail();
					String SL=bo.getString("MKSL").isEmpty()?"0":bo.getString("MKSL");
					String DJ=bo.getString("MKDJ").isEmpty()?"0":bo.getString("MKDJ");
					String CLF=bo.getString("CLF").isEmpty()?"0":bo.getString("CLF");
					String RGF=bo.getString("RGF").isEmpty()?"0":bo.getString("RGF");
					String WXWG=bo.getString("WXWG").isEmpty()?"0":bo.getString("WXWG");
					String CGF=bo.getString("CGF").isEmpty()?"0":bo.getString("CGF");
					String ZFWZYF=bo.getString("ZFWZYF").isEmpty()?"0":bo.getString("ZFWZYF");
					BigDecimal dj=new BigDecimal(DJ);
					BigDecimal clf=new BigDecimal(CLF);
					BigDecimal rg=new BigDecimal(RGF);
					BigDecimal wx=new BigDecimal(WXWG);
					BigDecimal cg=new BigDecimal(CGF);
					BigDecimal zf=new BigDecimal(ZFWZYF);
					BigDecimal sl=new BigDecimal(SL);
					if(null!=data) {

						data.set("MKXH", row.getString("MKXH"));
						data.set("XTMC", row.getString("XTMC"));
						data.set("MKMC", row.getString("MKMC"));
						data.set("XDXH", row.getString("XDXH"));
						data.set("MKBH", row.getString("MKBH"));
						data.set("SL", sl);
						data.set("DW", row.getString("DW"));
						data.set("DJ",  dj);
						data.set("ZJ",  dj.multiply(sl));
						data.set("GG", row.getString("GG"));
						data.set("ZFWZYF", zf);
						data.set("ZZFWZYF",   zf.multiply(sl));
						data.set("ZGSF",  rg.multiply(sl) );
						data.set("ZCLF",  clf.multiply(sl));
						data.set("ZWX",   wx.multiply(sl));
						data.set("ZCGF",  cg.multiply(sl));
						data.set("CLF",  clf);
						data.set("RGF",  rg);
						data.set("WXWG", wx);
						data.set("CGF", cg);
						data.set("XTMH", row.getString("XTMH"));
						System.out.println("data:"+data.toString());
						SDK.getBOAPI().update("BO_EU_CPQ_PROJECT_MSG_GRIDS", data);
					}else{
						BO newData=new BO();
						newData.set("MKXH", row.getString("MKXH"));
						newData.set("XTMC", row.getString("XTMC"));
						newData.set("MKMC", row.getString("MKMC"));
						newData.set("XDXH", row.getString("XDXH"));
						newData.set("MKBH", row.getString("MKBH"));
						newData.set("SL", sl);
						newData.set("DW", row.getString("DW"));
						newData.set("DJ", dj);
						newData.set("ZJ", dj.multiply(sl));
						newData.set("GG", row.getString("GG"));
						newData.set("ZFWZYF", zf);
						newData.set("ZZFWZYF", zf.multiply(sl));
						newData.set("ZGSF",rg.multiply(sl) );
						newData.set("ZCLF", clf.multiply(sl));
						newData.set("ZWX", wx.multiply(sl));
						newData.set("ZCGF", cg.multiply(sl));
						newData.set("CLF", clf);
						newData.set("RGF", rg);
						newData.set("WXWG", wx);
						newData.set("CGF", cg);
						newData.set("XTMH", row.getString("XTMH"));
						SDK.getBOAPI().create("BO_EU_CPQ_PROJECT_MSG_GRIDS", newData, bindId,userContext.getUID() );
					}
					//对新增和修改的参数信息进行修改
					List<String> sqlList1=new ArrayList<>();
					List<BO> sqlLists1=new ArrayList<>();
					if(changeMap.size()>0) {
						for (String key : changeMap.keySet()) {
							String sql="update BO_EU_CPQ_PROJECT_MSG_PARAMETER_GRIDS set PARMENTIER_VALUE='"+changeMap.get(key)+"' where ID='"+key+"'";
							sqlList1.add(sql);
						}
					}
					if(addMap.size()>0) {
						List<BO> bos=SDK.getBOAPI().query("BO_EU_CPQ_PROJECT_PARAMETER_MAP").list();
						Map<String, String> csMap=new HashMap<>();
						if(bos.size()>0) {
							for (BO bo2 : bos) {
								csMap.put(bo2.getString("PARMENTIER_NO"), bo2.getString("SSXT"));
							}
						}
						for (String key : addMap.keySet()) {
							BO bo2=new BO();
					        JSONObject json=addMap.get(key);
							bo2.set("PARMENTIER_NO", json.getString("PARMENTIER_NO"));
							//bo2.set("XTMC", );
							bo2.set("PARMENTIER_NAME", json.getString("PARMENTIER_NAME"));
							bo2.set("PARMENTIER_VALUE",json.getString("PARMENTIER_VALUE") );
							bo2.set("DW", json.getString("DW"));
							bo2.set("BZ", json.getString("BZ"));
							bo2.set("XH", json.getString("XH"));
							if(csMap.containsKey(key)) {
								bo2.set("XTMC", csMap.get(key));
							}

							sqlLists1.add(bo2);
						}
					}
					if(sqlList1.size()>0) {
						System.out.println("修改模块信息："+sqlList1.toString());
						 String[] array3 = sqlList1.toArray(new String[sqlList1.size()]);
						 DBSql.batch(array3);
					}
					if(sqlLists1.size()>0) {
						SDK.getBOAPI().create("BO_EU_CPQ_PROJECT_MSG_PARAMETER_GRIDS", sqlLists1, bindId, userContext.getUID());
					}
			} catch (Exception e) {
				throw e;
			}finally {
				try {
					workbookwl.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}

		}else {

			try {
				BO bo=SDK.getBOAPI().query("BO_EU_CPQ_MODULE_MSG").bindId(pid).detail();
			JSONArray list=JSONObject.parseArray(datas);

			/**
			 * 对当前模块进行计算
			 */
			//获取模板参数信息

			List<BO> mblists=SDK.getBOAPI().query("BO_EU_CPQ_PROJECT_MSG_PARAMETER_GRIDS").bindId(bindId).list();
			Map<String, BO> mbMap=new HashMap<>();
			Map<String, JSONObject> pMap = new HashMap<String, JSONObject>();
			Map<String, String> nameMap = new HashMap<String, String>();
			//Map<String, String> noMap = new HashMap<>();
			if(mblists.size()>0) {
				for (int i = 0; i < mblists.size(); i++) {
					BO bo2 = mblists.get(i);
					mbMap.put(bo2.getString("PARMENTIER_NO"), bo2);
					String bh =bo2.getString("PARMENTIER_NO");
					String value =  bo2.getString("PARMENTIER_VALUE");
					String name =  bo2.getString("PARMENTIER_NAME");
					String dw =  bo2.getString("DW");
					JSONObject jsonObject=new JSONObject();
					//判断value是否为数据
					if( NumberUtils.isCreatable(value)) {
						jsonObject.put("type", "1");//值为1者表示value为数值
					}else {
						jsonObject.put("type", "0");//值为0者表示value为字符串
					}
					jsonObject.put("value", value);
					pMap.put(bh, jsonObject);
					System.out.println("jsonObject:"+jsonObject);
					nameMap.put(bh, name);

				}
			}

			//存放修改后的参数信息
			Map<String, String> changeMap = new HashMap<String, String>();
			Map<String, String> changeMap1 = new HashMap<String, String>();
			//存放新增的参数信息
			Map<String, JSONObject> addMap = new HashMap<String, JSONObject>();
		if(list.size()>0) {
			for (int i = 0; i < list.size(); i++) {
				JSONObject json = (JSONObject) list.get(i);
				//BO json = list.get(i);
				String bh = (String) json.get("PARMENTIER_NO");
				String value = (String) json.get("PARMENTIER_VALUE");
				String name = (String) json.get("PARMENTIER_NAME");
				String dw = (String) json.get("DW");
				JSONObject jsonObject=new JSONObject();
				//判断value是否为数据
				if( NumberUtils.isCreatable(value)) {
					jsonObject.put("type", "1");//值为1者表示value为数值
				}else {
					jsonObject.put("type", "0");//值为0者表示value为字符串
				}
				jsonObject.put("value", value);
				jsonObject.put("PARMENTIER_NO", bh);
				pMap.put(bh, jsonObject);
				System.out.println("pMap:"+pMap);
				nameMap.put(bh, name);
				if(mbMap.containsKey(bh)) {
					BO bo2=mbMap.get(bh);
					String oldvalue=bo2.getString("PARMENTIER_VALUE");
					if(!oldvalue.equals(value)) {
						changeMap.put(bo2.getId(), value);
						changeMap1.put(bh, value);
					}
				}else {
					addMap.put(bh, json);
					//int num =mblists.size()+ i + 1;
					//noMap.put(bh, "B$" + num);
				}
			}
		}

			// 设置参数值到单元格中
				JSONArray cslist = new JSONArray();
				for (String key : pMap.keySet()) {
					cslist.add(pMap.get(key));
				}
				Sheet sheet = workbookwl.createSheet("Sheet1");
				Map<String, String> noMap = new HashMap<>();
				for (int i = 0; i < cslist.size(); i++) {
					int num = i + 1;
					Cell cellB1 = sheet.createRow(i).createCell(1);
					JSONObject jsonObject = (JSONObject) cslist.get(i);
					System.out.println("参数信息"+jsonObject);
					// BO jsonObject = list.get(i);
					String bh = (String) jsonObject.get("PARMENTIER_NO");
					String type = jsonObject.getString("type");
					if (type.equals("0")) {
						cellB1.setCellValue(jsonObject.getString("value"));
						System.out.println(bh+"-cellB1:" + cellB1.getStringCellValue());
					} else {
						String value = jsonObject.getString("value");
						double numValue = Double.parseDouble(value);
						cellB1.setCellValue(numValue);
						System.out.println(bh+"-cellB1:" + cellB1.getNumericCellValue());
					}
					noMap.put(bh, "B$" + num);
				}
	         //获取对应的模块信息,存在修改的参数信息
				List<RowMap> maps=new ArrayList<>();
				if(changeMap1.size()>0) {
					String keys="";
					for (String key : changeMap1.keySet()) {
						if(keys.isEmpty()) {
							keys="'"+key+"'";
						}else {
							keys+=",'"+key+"'";
						}
					}
					String  modelSql="select   k.*,h.ID,h.WLMC,h.WLBM,h.WLJSGS,h.CLF,h.RGF,h.WXWG,h.CGF,h.DJ,"
							+ "h.ZJ,h.ZCGF,h.ZGSF,h.ZWX,h.ZCLF,h.BINDID from  "
							+ "BO_EU_CPQ_MODULE_MSG_GRID h,("
							+ "select a.ID as BID,a.XDXH,a.MKBH,a.SL,b.BINDID,b.SLJSGZ,b.MKSL "
							+ "from  BO_EU_CPQ_PROJECT_MSG_GRIDS a, " +
							"(select  c.MKBM,n.PARMENTIER_NO,c.BINDID,c.SLJSGZ,c.MKSL from  "
							+ "BO_EU_CPQ_MODULE_MSG c,BO_EU_CPQ_MODULE_MSG_PRICE_PARAMETERS n "
							+ "where c.BINDID=n.BINDID and c.STATUS='启用') b  "
							+ "where a.MKBH=b.MKBM and a.MKBH!=? and  a.BINDID=?  and b.PARMENTIER_NO in("+keys+") ) k "
							+ "where h.BINDID=k.BINDID";
					 maps=DBSql.getMaps(modelSql, new Object[] {bo.getString("MKBM"),bindId});
				    if(maps.size()>0) {
				    	Map<String, List<RowMap>> idMap = new HashMap<>();
				    	for (RowMap rowMap : maps) {
				    		String bm=rowMap.getString("MKBM");
				    		if(idMap.containsKey(bm)) {
				    		List<RowMap>	bms=idMap.get(bm);
				    		bms.add(rowMap);
				    		idMap.put(bm, bms);
				    		}else {
				    			List<RowMap> bMaps=new ArrayList<RowMap>();
				    			bMaps.add(rowMap);
				    			idMap.put(rowMap.getString("MKBM"), bMaps);
				    		}

						}
				    	//对修改的模块进行计算
				    	String sql="update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ=?,CLF=?,RGF=?,WXWG=?,CGF=?,ZFWZYF=?,ZJ=?,ZCLF=?,ZGSF=?,ZWX=?,ZCGF=?,ZZFWZYF=?,SL=? where ID=?";
				    	for (String key1 : idMap.keySet()) {
				    		List<RowMap> materMap = new ArrayList<>();
							List<RowMap> rowMaps=idMap.get(key1);
							double MKDJ = 0;// 期货价
							double CLF = 0;// 期货价材料费
							double RGF = 0;// 人工费
							double WXWG = 0;// 外协费
							double CGF = 0;// 采购费
							for (int i = 0; i < rowMaps.size(); i++) {
								  RowMap rowMap=rowMaps.get(i);
								  String equation = rowMap.getString("WLJSGS");
								  // 添加空值检查
								  if (equation == null || equation.trim().isEmpty()) {
									  System.out.println("警告：WLJSGS字段为空，跳过处理");
									  continue;
								  }
								  Document document = Jsoup.parse(equation);
								  String newQuation=document.text();
								  // 添加newQuation的空值检查
								  if (newQuation == null) {
									  System.out.println("警告：解析后的公式为空，跳过处理");
									  continue;
								  }
								  if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
									  System.out.println("参数计算公式："+newQuation);
										// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
										for (String key : noMap.keySet()) {
											// 添加key的空值检查
											if (key != null) {
												int num = newQuation.indexOf(key);
												if (num > -1) {
													// 添加noMap.get(key)的空值检查
													String replacement = noMap.get(key);
													if (replacement != null) {
														newQuation = newQuation.replace(key, replacement);
													} else {
														System.out.println("警告：参数 " + key + " 在noMap中未找到对应的替换值，跳过替换");
													}
												}
											}
										}
										System.out.println("转换后的参数计算公式："+newQuation);
										// 计算出物料数量和当前模块的物料成本价格
										FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
										Cell cellB1 = sheet.createRow(cslist.size()+i).createCell(2);
										cellB1.setCellFormula(newQuation);
										formulaEvaluator.evaluateFormulaCell(cellB1);
										// 获取公式计算结果
										CellValue cellValue = formulaEvaluator.evaluate(cellB1);
										System.out.println("The result of the formula is: " + cellValue.getNumberValue());
										String price1 = rowMap.getString("DJ").isEmpty()?"0":rowMap.getString("DJ");
										//String zl = bo.getString("ZL").isEmpty()?"0":bo.getString("ZL");
										//String tj = bo.getString("TJ").isEmpty()?"0":bo.getString("TJ");
										String clf = rowMap.getString("CLF").isEmpty()?"0":rowMap.getString("CLF");
										String rgf = rowMap.getString("RGF").isEmpty()?"0":rowMap.getString("RGF");
										String wx = rowMap.getString("WXWG").isEmpty()?"0":rowMap.getString("WXWG");
										String cgf = rowMap.getString("CGF").isEmpty()?"0":rowMap.getString("CGF");
										double total1 = Double.parseDouble(price1) * cellValue.getNumberValue();
										//double zltotal = Double.parseDouble(zl) * cellValue.getNumberValue();
										//double tjtotal = Double.parseDouble(tj) * cellValue.getNumberValue();
										double clftotal1 = Double.parseDouble(clf) * cellValue.getNumberValue();
										double rgftotal = Double.parseDouble(rgf) * cellValue.getNumberValue();
										double wxtotal = Double.parseDouble(wx) * cellValue.getNumberValue();
										double cgftotal = Double.parseDouble(cgf) * cellValue.getNumberValue();
										System.out.println("物料名称:"+rowMap.getString("WLMC")+"单价："+price1+",总价："+total1);
										// 将数量和成本价格放入物料map集合中

										rowMap.put("SL", cellValue.getNumberValue());
										rowMap.put("ZJ", total1);
										//bo.set("ZZL", zltotal);
										//bo.set("ZTJ", tjtotal);
										rowMap.put("ZCGF", cgftotal);
										rowMap.put("ZGSF", rgftotal);
										rowMap.put("ZWX", wxtotal);
										rowMap.put("ZCLF",clftotal1);

								  }
								  materMap.add(rowMap);
							}
							// 计算出模块的单价，每个物料总价相加
							double mkPrice1 = 0;
							double zcgfPrice = 0;
							double zgsfPrice = 0;
							double zwxPrice = 0;
							double zclfPrice1 = 0;
							for (RowMap rowMap : materMap) {
								String zjString1 = rowMap.getString("ZJ");
								String zcgfString = rowMap.getString("ZCGF");
								String zgsfString = rowMap.getString("ZGSF");
								String zwxString = rowMap.getString("ZWX");
								String zclfString1 = rowMap.getString("ZCLF");
								mkPrice1 = mkPrice1 + Double.parseDouble(zjString1);
								zcgfPrice = zcgfPrice + Double.parseDouble(zcgfString);
								zgsfPrice = zgsfPrice + Double.parseDouble(zgsfString);
								zwxPrice = zwxPrice + Double.parseDouble(zwxString);
								zclfPrice1 = zclfPrice1 + Double.parseDouble(zclfString1);
							}

						   //计算直发物资运费
								RowMap data=rowMaps.get(0);
								String processId=data.getString("BINDID");
								List<BO> zfList=SDK.getBOAPI().query("BO_EU_BO_EU_CPQ_MODULE_MSG_STRAIGHT_HAIR").bindId(processId).list();
								double ZFYF = 0;// 期货价
								if(zfList.size()>0) {
		                	  for (int i = 0; i < zfList.size(); i++) {
		                		  BO bo2=zfList.get(i);
								String sljsgs=bo2.getString("JSGS");//直发运费数量计算公式；
		                		  String yfdj=bo2.getString("YFBZ").isEmpty()?"0":bo2.getString("YFBZ");//运费单价；
		                		  String SL=bo2.getString("SL").isEmpty()?"0":bo2.getString("SL");//运费单价；
		                		  double price =Double.parseDouble(yfdj);
		                		  //double sl =Double.parseDouble(SL);
		 	        			 //BigDecimal sl = new BigDecimal(SL);
		                		 // double zj =price*sl;
							   // ZFYF=ZFYF+zj;
								// 添加空值检查
								if (sljsgs == null || sljsgs.trim().isEmpty()) {
									System.out.println("警告：JSGS字段为空，跳过处理");
									continue;
								}
								Document document = Jsoup.parse(sljsgs);
								String newQuation=document.text();
								// 添加newQuation的空值检查
								if (newQuation == null) {
									System.out.println("警告：解析后的公式为空，跳过处理");
									continue;
								}
								 if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
									  for (String key : pMap.keySet()) {
											// 添加key的空值检查
											if (key != null) {
												int num = newQuation.indexOf(key);
												if (num > -1) {
													// 添加noMap.get(key)的空值检查
													String replacement = noMap.get(key);
													if (replacement != null) {
														newQuation = newQuation.replace(key, replacement);
													} else {
														System.out.println("警告：参数 " + key + " 在noMap中未找到对应的替换值，跳过替换");
													}
												}
											}
										}
									  FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
										Cell cellB1 = sheet.createRow(cslist.size()+rowMaps.size()+zfList.size()+1+i).createCell(2);
										cellB1.setCellFormula(newQuation);
										formulaEvaluator.evaluateFormulaCell(cellB1);
										// 获取公式计算结果
										CellValue cellValue = formulaEvaluator.evaluate(cellB1);
										System.out.println("The result of the formula is: " + cellValue.getNumberValue());
										ZFYF=ZFYF+cellValue.getNumberValue()*price;
							}else {
								String sl=data.getString("SL").isEmpty()?"0":data.getString("SL");//运费数量;
								 double num=Double.parseDouble(sl);
								ZFYF=ZFYF+num*price;
							}
		                	  }
		                  }
								MKDJ = mkPrice1+ZFYF;
								CLF = zclfPrice1;
								RGF = zgsfPrice;
								WXWG = zwxPrice;
								CGF = zcgfPrice;

								//计算模块数量

							String jsgs=data.getString("SLJSGZ");//模块数量计算公式；
							// 添加空值检查
							if (jsgs == null || jsgs.trim().isEmpty()) {
								System.out.println("警告：SLJSGZ字段为空，跳过处理");
								jsgs = "0"; // 设置为默认值
							}
							Document document = Jsoup.parse(jsgs);
							String newQuation=document.text();
							// 添加newQuation的空值检查
							if (newQuation == null) {
								System.out.println("警告：解析后的公式为空，跳过处理");
								newQuation = "0"; // 设置为默认值
							}
							  if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
								  for (String key : pMap.keySet()) {
										// 添加key的空值检查
										if (key != null) {
											int num = newQuation.indexOf(key);
											if (num > -1) {
												// 添加noMap.get(key)的空值检查
												String replacement = noMap.get(key);
												if (replacement != null) {
													newQuation = newQuation.replace(key, replacement);
												} else {
													System.out.println("警告：参数 " + key + " 在noMap中未找到对应的替换值，跳过替换");
												}
											}
										}
									}
								  System.out.println("模块修改后的计算公式："+newQuation);
								  FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
									Cell cellB1 = sheet.createRow(cslist.size()+rowMaps.size()+2).createCell(2);
									cellB1.setCellFormula(newQuation);
									formulaEvaluator.evaluateFormulaCell(cellB1);
									// 获取公式计算结果
									CellValue cellValue = formulaEvaluator.evaluate(cellB1);
									System.out.println("The result of the formula is: " + cellValue.getNumberValue());
									double total1 = MKDJ * cellValue.getNumberValue();
									double clftotal1 = CLF * cellValue.getNumberValue();
									double rgftotal = RGF * cellValue.getNumberValue();
									double wxtotal =WXWG * cellValue.getNumberValue();
									double cgftotal = CGF * cellValue.getNumberValue();
									double yftotal = ZFYF * cellValue.getNumberValue();
								  DBSql.update(sql, new Object[] {MKDJ,CLF,RGF,WXWG,CGF,ZFYF,total1,clftotal1,rgftotal,wxtotal,cgftotal,yftotal,cellValue.getNumberValue(),data.getString("BID")});
							  }else {
								  String sl=data.getString("SL").isEmpty()?"0":data.getString("SL");//模块数量;
								  double num=Double.parseDouble(sl);
								  double total1 = MKDJ * num;
								  double clftotal1 = CLF * num;
								  double rgftotal = RGF * num;
								  double wxtotal =WXWG * num;
							      double cgftotal = CGF * num;
							      double yftotal = ZFYF * num;
								  DBSql.update(sql, new Object[] {MKDJ,CLF,RGF,WXWG,CGF,ZFYF,total1,clftotal1,rgftotal,wxtotal,cgftotal,yftotal,sl,data.getString("BID")});

							  }

						}


				    }
				}
				/**
				 * 计算当前模块数量及价格lists
				 */
				//模块物料明细
				List<BO> lists=SDK.getBOAPI().query("BO_EU_CPQ_MODULE_MSG_GRID").bindId(pid).list();
				//模块直发物资明细
				List<BO> zflists=SDK.getBOAPI().query("BO_EU_BO_EU_CPQ_MODULE_MSG_STRAIGHT_HAIR").bindId(pid).list();
				List<BO> materMap = new ArrayList<>();
				for (int i = 0; i < lists.size(); i++) {
					BO json =  lists.get(i);
					String equation = json.getString("WLJSGS");
					// 添加空值检查
					if (equation == null || equation.trim().isEmpty()) {
						equation = "0";
					}
					Document document = Jsoup.parse(equation);
					String newQuation=document.text();
					// 添加newQuation的空值检查
					if (newQuation == null) {
						System.out.println("警告：解析后的公式为空，跳过处理");
						continue;
					}
					if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
						  for (String key : pMap.keySet()) {
								// 添加key的空值检查
								if (key != null) {
									int num = newQuation.indexOf(key);
									if (num > -1) {
										// 添加noMap.get(key)的空值检查
										String replacement = noMap.get(key);
										if (replacement != null) {
											newQuation = newQuation.replace(key, replacement);
										} else {
											System.out.println("警告：参数 " + key + " 在noMap中未找到对应的替换值，跳过替换");
										}
									}
								}
							}
						// 计算出物料数量和当前模块的物料成本价格
							FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
							Cell cellB1 = sheet.createRow(cslist.size()+lists.size()+maps.size()+3+i).createCell(2);
							cellB1.setCellFormula(newQuation);
							formulaEvaluator.evaluateFormulaCell(cellB1);
							// 获取公式计算结果
							CellValue cellValue = formulaEvaluator.evaluate(cellB1);
							System.out.println("The result of the formula is: " + cellValue.getNumberValue());
							String price1 = json.getString("DJ").isEmpty()?"0":json.getString("DJ");
							//String zl = bo.getString("ZL").isEmpty()?"0":bo.getString("ZL");
							//String tj = bo.getString("TJ").isEmpty()?"0":bo.getString("TJ");
							String clf = json.getString("CLF").isEmpty()?"0":json.getString("CLF");
							String rgf = json.getString("RGF").isEmpty()?"0":json.getString("RGF");
							String wx = json.getString("WXWG").isEmpty()?"0":json.getString("WXWG");
							String cgf = json.getString("CGF").isEmpty()?"0":json.getString("CGF");
							double total1 = Double.parseDouble(price1) * cellValue.getNumberValue();
							//double zltotal = Double.parseDouble(zl) * cellValue.getNumberValue();
							//double tjtotal = Double.parseDouble(tj) * cellValue.getNumberValue();
							double clftotal1 = Double.parseDouble(clf) * cellValue.getNumberValue();
							double rgftotal = Double.parseDouble(rgf) * cellValue.getNumberValue();
							double wxtotal = Double.parseDouble(wx) * cellValue.getNumberValue();
							double cgftotal = Double.parseDouble(cgf) * cellValue.getNumberValue();
							// 将数量和成本价格放入物料map集合中

							json.set("SL", cellValue.getNumberValue());
							json.set("ZJ", total1);
							//bo.set("ZZL", zltotal);
							//bo.set("ZTJ", tjtotal);
							json.set("ZCGF", cgftotal);
							json.set("ZGSF", rgftotal);
							json.set("ZWX", wxtotal);
							json.set("ZCLF",clftotal1);
					}
					  materMap.add(json);
				}
				// 计算出模块的单价，每个物料总价相加
				double mkPrice1 = 0;
				double zcgfPrice = 0;
				double zgsfPrice = 0;
				double zwxPrice = 0;
				double zclfPrice1 = 0;
				double total1 = 0;
				double clftotal1 = 0;
				double rgftotal = 0;
				double wxtotal =0;
				double cgftotal = 0;
				double yftotal = 0;
				for (BO rowMap : materMap) {
					String zjString1 = rowMap.getString("ZJ");
					String zcgfString = rowMap.getString("ZCGF");
					String zgsfString = rowMap.getString("ZGSF");
					String zwxString = rowMap.getString("ZWX");
					String zclfString1 = rowMap.getString("ZCLF");
					mkPrice1 = mkPrice1 + Double.parseDouble(zjString1);
					zcgfPrice = zcgfPrice + Double.parseDouble(zcgfString);
					zgsfPrice = zgsfPrice + Double.parseDouble(zgsfString);
					zwxPrice = zwxPrice + Double.parseDouble(zwxString);
					zclfPrice1 = zclfPrice1 + Double.parseDouble(zclfString1);
				}
				double ZFYF =0;
				if(zflists.size()>0) {
					for (int i = 0; i < zflists.size(); i++) {
	          		  BO bo2=zflists.get(i);
						String sljsgs=bo2.getString("JSGS");//直发运费数量计算公式；
	          		  String yfdj=bo2.getString("YFBZ").isEmpty()?"0":bo2.getString("YFBZ");//运费单价；
            		  String SL=bo2.getString("SL").isEmpty()?"0":bo2.getString("SL");//运费单价；
            		  double price =Double.parseDouble(yfdj);
            		 // double sl =Double.parseDouble(SL);
	        			 //BigDecimal sl = new BigDecimal(SL);
            		 // double zj =price*sl;
				  //  ZFYF=ZFYF+zj;
						// 添加空值检查
						if (sljsgs == null || sljsgs.trim().isEmpty()) {
							System.out.println("警告：JSGS字段为空，跳过处理");
							continue;
						}
						Document document = Jsoup.parse(sljsgs);
						String newQuation=document.text();
						// 添加newQuation的空值检查
						if (newQuation == null) {
							System.out.println("警告：解析后的公式为空，跳过处理");
							continue;
						}
						 if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
							  for (String key : pMap.keySet()) {
									// 添加key的空值检查
									if (key != null) {
										int num = newQuation.indexOf(key);
										if (num > -1) {
											// 添加noMap.get(key)的空值检查
											String replacement = noMap.get(key);
											if (replacement != null) {
												newQuation = newQuation.replace(key, replacement);
											} else {
												System.out.println("警告：参数 " + key + " 在noMap中未找到对应的替换值，跳过替换");
											}
										}
									}
								}
							  FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
								Cell cellB1 = sheet.createRow(cslist.size()+lists.size()+maps.size()+zflists.size()+3+i).createCell(2);
								cellB1.setCellFormula(newQuation);
								formulaEvaluator.evaluateFormulaCell(cellB1);
								// 获取公式计算结果
								CellValue cellValue = formulaEvaluator.evaluate(cellB1);
								System.out.println("The result of the formula is: " + cellValue.getNumberValue());
								ZFYF=ZFYF+cellValue.getNumberValue()*price;
					}else {
						String sl=bo2.getString("SL").isEmpty()?"0":bo2.getString("SL");//运费数量;
						 double num=Double.parseDouble(sl);
						ZFYF=ZFYF+num*price;
					}
	          	  }
	            }
				//模块单价需要加上直发物资运费价格
				mkPrice1=mkPrice1+ZFYF;
				//计算模块数量
				String jsgs=bo.getString("SLJSGZ");//模块数量计算公式；
				// 添加空值检查
				if (jsgs == null || jsgs.trim().isEmpty()) {
					System.out.println("警告：SLJSGZ字段为空，跳过处理");
					jsgs = "0"; // 设置为默认值
				}
				Document document = Jsoup.parse(jsgs);
				String newQuation=document.text();
				// 添加newQuation的空值检查
				if (newQuation == null) {
					System.out.println("警告：解析后的公式为空，跳过处理");
					newQuation = "0"; // 设置为默认值
				}
				JSONObject row=JSONObject.parseObject(rowData);
				double slnum=0;
				if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
					  for (String key : pMap.keySet()) {
							// 添加key的空值检查
							if (key != null) {
								int num = newQuation.indexOf(key);
								if (num > -1) {
									// 添加noMap.get(key)的空值检查
									String replacement = noMap.get(key);
									if (replacement != null) {
										newQuation = newQuation.replace(key, replacement);
									} else {
										System.out.println("警告：参数 " + key + " 在noMap中未找到对应的替换值，跳过替换");
									}
								}
							}
						}
					    FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
						Cell cellB1 = sheet.createRow(cslist.size()+lists.size()+maps.size()+zflists.size()+5).createCell(2);
						cellB1.setCellFormula(newQuation);
						formulaEvaluator.evaluateFormulaCell(cellB1);
						// 获取公式计算结果
						CellValue cellValue = formulaEvaluator.evaluate(cellB1);
						System.out.println("当前模块The result of the formula is: " + cellValue.getNumberValue());
						 total1 = mkPrice1 * cellValue.getNumberValue();
						 clftotal1 = zclfPrice1 * cellValue.getNumberValue();
					     rgftotal = zgsfPrice * cellValue.getNumberValue();
						 wxtotal =zwxPrice * cellValue.getNumberValue();
						 cgftotal = zcgfPrice * cellValue.getNumberValue();
						 yftotal = ZFYF * cellValue.getNumberValue();
						slnum=cellValue.getNumberValue();
					  //DBSql.update(sql, new Object[] {MKDJ,CLF,RGF,WXWG,CGF,ZFYF,total1,clftotal1,rgftotal,wxtotal,cgftotal,yftotal,cellValue.getNumberValue()});
				  }else {
					  String sl=bo.getString("MKSL").isEmpty()?"0":bo.getString("MKSL");//模块数量;
					  double num=Double.parseDouble(sl);
					  slnum=num;
					   total1 = mkPrice1 * num;
					   clftotal1 = zclfPrice1 * num;
					   rgftotal = zgsfPrice * num;
					   wxtotal =zwxPrice * num;
				       cgftotal = zcgfPrice * num;
				       yftotal = ZFYF * num;
					  //DBSql.update(sql, new Object[] {MKDJ,CLF,RGF,WXWG,CGF,ZFYF,total1,clftotal1,rgftotal,wxtotal,cgftotal,yftotal,sl});

				  }

				String id=row.getString("ID");
				BO data=SDK.getBOAPI().query("BO_EU_CPQ_PROJECT_MSG_GRIDS").addQuery("ID=", id).detail();
				System.out.println("slnum:"+slnum);
				if(null!=data) {
					data.set("MKXH", row.getString("MKXH"));
					data.set("XTMC", row.getString("XTMC"));
					data.set("MKMC", row.getString("MKMC"));
					data.set("XDXH", row.getString("XDXH"));
					data.set("MKBH", row.getString("MKBH"));
					data.set("SL", slnum);
					data.set("DW", row.getString("DW"));
					data.set("DJ", mkPrice1);
					data.set("ZJ", total1);
					data.set("GG", row.getString("GG"));
					data.set("ZFWZYF", ZFYF);
					data.set("ZZFWZYF", yftotal);
					data.set("ZGSF",rgftotal );
					data.set("ZCLF", clftotal1);
					data.set("ZWX", wxtotal);
					data.set("ZCGF", cgftotal);
					data.set("CLF", zclfPrice1);
					data.set("RGF", zgsfPrice);
					data.set("WXWG", zwxPrice);
					data.set("CGF", zcgfPrice);
					data.set("XTMH", row.getString("XTMH"));
					SDK.getBOAPI().update("BO_EU_CPQ_PROJECT_MSG_GRIDS", data);
				}else{
					BO newData=new BO();
					newData.set("MKXH", row.getString("MKXH"));
					newData.set("XTMC", row.getString("XTMC"));
					newData.set("MKMC", row.getString("MKMC"));
					newData.set("XDXH", row.getString("XDXH"));
					newData.set("MKBH", row.getString("MKBH"));
					newData.set("SL", slnum);
					newData.set("DW", row.getString("DW"));
					newData.set("DJ", mkPrice1);
					newData.set("ZJ", total1);
					newData.set("GG", row.getString("GG"));
					newData.set("ZFWZYF", ZFYF);
					newData.set("ZZFWZYF", yftotal);
					newData.set("ZGSF",rgftotal );
					newData.set("ZCLF", clftotal1);
					newData.set("ZWX", wxtotal);
					newData.set("ZCGF", cgftotal);
					newData.set("CLF", zclfPrice1);
					newData.set("RGF", zgsfPrice);
					newData.set("WXWG", zwxPrice);
					newData.set("CGF", zcgfPrice);
					newData.set("XTMH", row.getString("XTMH"));
					SDK.getBOAPI().create("BO_EU_CPQ_PROJECT_MSG_GRIDS", newData, bindId,userContext.getUID() );
				}
				//对新增和修改的参数信息进行修改
				List<String> sqlList=new ArrayList<>();
				List<BO> sqlLists=new ArrayList<>();
				if(changeMap.size()>0) {
					for (String key : changeMap.keySet()) {
						String sql="update BO_EU_CPQ_PROJECT_MSG_PARAMETER_GRIDS set PARMENTIER_VALUE='"+changeMap.get(key)+"' where ID='"+key+"'";
						sqlList.add(sql);
					}
				}
				if(addMap.size()>0) {
					List<BO> bos=SDK.getBOAPI().query("BO_EU_CPQ_PROJECT_PARAMETER_MAP").list();
					Map<String, String> csMap=new HashMap<>();
					if(bos.size()>0) {
						for (BO bo2 : bos) {
							csMap.put(bo2.getString("PARMENTIER_NO"), bo2.getString("SSXT"));
						}
					}
					for (String key : addMap.keySet()) {
						BO bo2=new BO();
				        JSONObject json=addMap.get(key);
						bo2.set("PARMENTIER_NO", json.getString("PARMENTIER_NO"));
						//bo2.set("XTMC", );
						bo2.set("PARMENTIER_NAME", json.getString("PARMENTIER_NAME"));
						bo2.set("PARMENTIER_VALUE",json.getString("PARMENTIER_VALUE") );
						bo2.set("DW", json.getString("DW"));
						bo2.set("BZ", json.getString("BZ"));
						bo2.set("XH", json.getString("XH"));
						if(csMap.containsKey(key)) {
							bo2.set("XTMC", csMap.get(key));
						}
						sqlLists.add(bo2);
					}
				}
				if(sqlList.size()>0) {
					 String[] array3 = sqlList.toArray(new String[sqlList.size()]);
					 DBSql.batch(array3);
				}
				if(sqlLists.size()>0) {
					SDK.getBOAPI().create("BO_EU_CPQ_PROJECT_MSG_PARAMETER_GRIDS", sqlLists, bindId, userContext.getUID());
				}

			} catch (Exception e) {
				throw  e;
			}finally {
				try {
					workbookwl.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}



		}

		return ro.toString();


	}

}
